package com.pms.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;



public class DBHelp<T> {

	public static final String DRIVER = "com.mysql.jdbc.Driver";
	public static final String URL = "jdbc:mysql:///mydb";
	public static final String DBNAME = "root";
	public static final String DBPASSWORD = "root";
	
	/**
	 * 锟斤拷取锟斤拷菘锟斤拷锟斤拷佣锟斤拷锟�
	 * @return Connection锟斤拷锟斤拷
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Connection getConnection() throws ClassNotFoundException, SQLException {
		Class.forName(DRIVER);
		return DriverManager.getConnection(URL,DBNAME,DBPASSWORD);
	}
	
	
	/**
	 * 执锟斤拷insert update delete锟斤拷锟�
	 * @param sql 
	 * @param objs 锟斤拷锟斤拷应锟侥诧拷锟斤拷值
	 * @return 锟斤拷执锟斤拷影锟斤拷锟斤拷锟斤拷锟�
	 */
	public int executeSQL(String sql,Object... objs) {
		int rows = 0;
		Connection conn = null;
		PreparedStatement stat = null;
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			
			for (int i = 0; i < objs.length; i++) {
				stat.setObject(i+1, objs[i]);
			}
			rows = stat.executeUpdate();
			System.out.println("SQL:" + sql);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(stat,conn);
		}
		return rows;
	}
	
	public Number executeQueryToNumber(String sql,Object... objs) {
	
		double result = 0;
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			
			for (int i = 0; i < objs.length; i++) {
				stat.setObject(i + 1, objs[i]);
			}
			rs = stat.executeQuery();
			
			if(rs.next()){
				result = rs.getDouble(1);
			}
			System.out.println("SQL:" + sql);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs,stat,conn);
		}
		return result;
	}
	
	public T executeQueryToObject(String sql,RowMapper<T> rowMapper,Object... args) {
		
		T result = null;
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i + 1, args[i]);
			}
			rs = stat.executeQuery();
			
			if(rs.next()) {
				result = rowMapper.mapRow(rs);
			}
			System.out.println("SQL:" + sql);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs,stat,conn);
		}
		return result;
	}
	
	public List<T> executeQueryToList(String sql,RowMapper<T> rowMapper,Object... args) {
		
		List<T> list = new ArrayList<T>();
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i + 1, args[i]);
			}
			rs = stat.executeQuery();
			
			while(rs.next()) {
				T result = rowMapper.mapRow(rs);
				list.add(result);
			}
			System.out.println("SQL:" + sql);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs,stat,conn);
		}
		return list;
	}
	
	
	
	
	/**
	 * 锟酵凤拷锟斤拷菘锟斤拷锟皆�
	 * @param stat
	 * @param conn
	 */
	public void close(Statement stat,Connection conn) {
		close(null,stat,conn);
	}
	
	/**
	 * 锟酵凤拷锟斤拷菘锟斤拷锟皆�
	 * @param rs
	 * @param stat
	 * @param conn
	 */
	public void close(ResultSet rs,Statement stat,Connection conn) {
		try {
			if(rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(stat != null) {
					stat.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if(conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	
	
}
